package com.jiuyun.dao.impi;

import com.jiuyun.dao.StudentaDao;
import com.jiuyun.entity.*;

import com.jiuyun.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class StudentaDaoimpl implements StudentaDao {

    QueryRunner runner =new QueryRunner();
    @Override
    public List<Studenta> getAll() throws SQLException {
        String sql="SELECT * FROM student";
        Connection conn= AliPool.getConn();
        List<Studenta> list=runner.query(conn,sql,new BeanListHandler<Studenta>(Studenta.class));
        conn.close();
        return list;
    }


    public int getadd(Studenta s) throws SQLException {
        String sql="INSERT INTO student(stid,classid,stname,stpass,stsex) VALUES(NULL,?,?,?,?)";
        Connection conn= AliPool.getConn();
        int ok=runner.update(conn,sql,s.getClassid(),s.getStname(),s.getStpass(),s.getStsex());
        conn.close();
        return ok;
    }

    @Override
    public int getdel(int id) throws SQLException {
        String sql="DELETE FROM student WHERE stid=?";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,id);
        conn.close();
        return ok;
    }

    @Override
    public int update(int calssa, String name, String pass, String sex, int id) throws SQLException {
        String sql="UPDATE  student SET classid=?,stname=?,stpass=?,stsex=? WHERE stid=?";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,calssa,name,pass,sex,id);
        conn.close();
        return ok;
    }

    @Override//通过id查询所有
    public List<Studenta> getatAll(int sid) throws SQLException {
        String sql="SELECT * FROM student WHERE stid=?";
        Connection conn=AliPool.getConn();
        List<Studenta> list=runner.query(conn,sql,new BeanListHandler<Studenta>(Studenta.class),sid);
        conn.close();
        return list;
    }
    //-----------------------------------------班级dao-----------------------------------------------------
    @Override
    public List<Classa> banjiAll() throws SQLException {
        String sql="SELECT * FROM classes";
        Connection conn=AliPool.getConn();
        List<Classa> list=runner.query(conn,sql, new BeanListHandler<Classa>(Classa.class));
        conn.close();
        return list;
    }

    @Override
    public int banjiadd(Classa c) throws SQLException {
        String sql="INSERT INTO classes(classId,className,classCreate,classRemark,classState,bzr) VALUES(NULL,?,?,?,?,?)";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql, c.getClassName(), c.getClassCreate(), c.getClassRemark(),c.getClassState(),c.getBzr());
        conn.close();
        return ok;
    }

    @Override
    public int banjidel(int id) throws SQLException {
        String sql="DELETE  FROM classes WHERE classId=?";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,id);
        conn.close();
        return ok;
    }

    @Override
    public int updatebanji(String className, String classCreate, String classRemark, int classState, int bzr, int classid) throws SQLException {
        String sql="UPDATE classes SET className=?,classCreate=?,classRemark=?,classState=?,bzr=? WHERE classId=?";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,className,classCreate,classRemark,classState,bzr,classid);
        conn.close();
        return ok;
    }

    @Override
    public List<Classa> banjitjAll(int cid) throws SQLException {
        String sql="SELECT * from classes WHERE classId=?";
        Connection conn=AliPool.getConn();
        List<Classa> list=runner.query(conn,sql,new BeanListHandler<Classa>(Classa.class),cid);
        conn.close();
        return list;
    }
    //------------------------------------------------------------------------------------------------------------
    @Override
    public List<Teachar> laoshuAll() throws SQLException {
        String sql="SELECT * FROM teacher";
        Connection conn=AliPool.getConn();
        List<Teachar> list=runner.query(conn,sql, new BeanListHandler<Teachar>(Teachar.class));
        conn.close();
        return list;

    }

    @Override
    public int laoshuadd(Teachar t) throws SQLException {
        String sql="INSERT INTO teacher(teacherid,tname,tpass,tphone,tjob)VALUES(NULL,?,?,?,?)";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,t.getTname(),t.getTpass(),t.getTphone(),t.getTjob());
        conn.close();
        return ok;

    }

    @Override
    public int laoshudel(int id) throws SQLException {
        String sql="DELETE FROM teacher WHERE teacherid=?";
        Connection conn= AliPool.getConn();
        int ok=runner.update(conn,sql,id);
        conn.close();
        return ok;
    }

    @Override
    public int updatelaoshu(String tname, String tpass, String tphone, String tjob, int teacharid) throws SQLException {
        String sql="UPDATE teacher SET tname=?,tpass=?,tphone=?,tjob=? WHERE teacherid=?";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,tname,tpass,tphone,tjob,teacharid);
        conn.close();
        return ok;
    }

    @Override
    public List<Teachar> laoshutjAll(int tid) throws SQLException {
        String sql="SELECT * from teacher WHERE teacherid=?";
        Connection conn=AliPool.getConn();
        List<Teachar> list=runner.query(conn,sql,new BeanListHandler<Teachar>(Teachar.class),tid);
        conn.close();
        return list;
    }

    @Override
    public PerUser glydenglu(String name, String pass) throws SQLException {
        String sql="SELECT * FROM `user` WHERE uname=? AND upass=?";
        Connection conn=AliPool.getConn();
        PerUser user=runner.query(conn,sql,new BeanHandler<PerUser>(PerUser.class),name,pass);
        conn.close();
        return user;
    }

    @Override
    public Studenta xxdenglu(String name, String pass) throws SQLException {
        String sql="SELECT * FROM student WHERE  stname=? AND stpass=?";
        Connection conn=AliPool.getConn();
        Studenta studenta= runner.query(conn,sql,new BeanHandler<Studenta>(Studenta.class),name,pass);
        conn.close();
        return studenta;
    }

    @Override
    public Teachar laoshidenglu(String name, String pass) throws SQLException {
        String sql="SELECT * FROM teacher WHERE tname=? AND tpass=?";
        Connection conn=AliPool.getConn();
        Teachar teachar=runner.query(conn,sql, new  BeanHandler<Teachar>(Teachar.class),name,pass);
        conn.close();
        return teachar;
    }

    @Override
    public List<Userrole> quanxian() throws SQLException {
        String sql="SELECT `user`.uname,`user`.upass,`user`.user_role_id,role.role_desc FROM `user`  INNER JOIN role ON `user`.user_role_id=role.role_id";
        Connection conn=AliPool.getConn();
        List<Userrole> list=runner.query(conn,sql, new BeanListHandler<Userrole>(Userrole.class));
        conn.close();
        return list;
    }

    @Override
    public List<Userrole> denglu(String name, String pass) throws SQLException {
        String sql="SELECT `user`.uid,`user`.uname,`user`.upass,`user`.user_role_id,role.role_desc FROM `user` INNER JOIN role ON `user`.user_role_id=role.role_id WHERE `user`.uname=? AND `user`.upass=?";
        Connection conn=AliPool.getConn();
        List<Userrole>  list=runner.query(conn,sql,new BeanListHandler<Userrole>(Userrole.class),name,pass);
        conn.close();
        return list;
    }

    @Override
    public int zhuce(User u) throws SQLException {
        String sql="INSERT INTO `user`(uname,upass,user_role_id) VALUES(?,?,?)";
        Connection conn=AliPool.getConn();
        int ok=runner.update(conn,sql,u.getUname(),u.getUpass(),u.getUser_role_id());
        conn.close();
        return ok;

    }

    @Override
    public Userrole grxx(int uid) throws SQLException {
        String sql="select * from `user` where uid=?";
        Connection conn=AliPool.getConn();
        Userrole list=runner.query(conn,sql, new BeanHandler<Userrole>(Userrole.class),uid);
        conn.close();
        return list;
    }


}
